package com.example.qrcodecamera;

// DatabaseHelper.java
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import java.util.ArrayList;
import java.util.List;

public class DatabaseHelper extends SQLiteOpenHelper {
    // 账号数据库
    private static final String DATABASE_NAME = "accounts.db";
    private static final int DATABASE_VERSION = 2;

    // 用户表
    public static final String TABLE_USERS = "users";
    public static final String COLUMN_USER_ID = "id";
    public static final String COLUMN_USER_USERNAME = "username";
    public static final String COLUMN_USER_PASSWORD = "password";

    // 管理员表
    public static final String TABLE_ADMINS = "admins";
    public static final String COLUMN_ADMIN_ID = "id";
    public static final String COLUMN_ADMIN_USERNAME = "username";
    public static final String COLUMN_ADMIN_PASSWORD = "password";

    // 记录表 - 为了兼容性保留
    private static final String TABLE_RECORDS = "records";

    public static final String TABLE_MYSQL = "mysql_records";
    // 创建用户表的SQL语句
    private static final String CREATE_TABLE_USERS = "CREATE TABLE " + TABLE_USERS + "("
            + COLUMN_USER_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + COLUMN_USER_USERNAME + " TEXT,"
            + COLUMN_USER_PASSWORD + " TEXT" + ")";

    // 创建管理员表的SQL语句
    private static final String CREATE_TABLE_ADMINS = "CREATE TABLE " + TABLE_ADMINS + "("
            + COLUMN_ADMIN_ID + " INTEGER PRIMARY KEY AUTOINCREMENT,"
            + COLUMN_ADMIN_USERNAME + " TEXT,"
            + COLUMN_ADMIN_PASSWORD + " TEXT" + ")";

    // 创建记录表的SQL语句 - 为了兼容性保留
    private static final String CREATE_TABLE_RECORDS = "CREATE TABLE " + TABLE_RECORDS + " (" +
            "id INTEGER PRIMARY KEY AUTOINCREMENT," +
            "date TEXT," +
            "batch_number TEXT," +
            "number TEXT," +
            "weight1 TEXT," +
            "weight2 TEXT," +
            "weight3 TEXT)";


    private static final String CREATE_TABLE_MYSQL = "CREATE TABLE " + TABLE_MYSQL + " (" +
            "id INTEGER PRIMARY KEY AUTOINCREMENT," +
            "date TEXT," +
            "batch_number TEXT," +
            "number TEXT," +
            "weight1 TEXT," +
            "weight2 TEXT," +
            "weight3 TEXT)";
    public DatabaseHelper(Context context) {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db) {
        db.execSQL(CREATE_TABLE_USERS);
        db.execSQL(CREATE_TABLE_ADMINS);
        db.execSQL(CREATE_TABLE_RECORDS);
        db.execSQL(CREATE_TABLE_MYSQL);
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_USERS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_ADMINS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_RECORDS);
        db.execSQL("DROP TABLE IF EXISTS " + TABLE_MYSQL);
        onCreate(db);
    }

    // 添加用户
    public long addUser(String username, String password) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_USER_USERNAME, username);
        values.put(COLUMN_USER_PASSWORD, password);
        long id = db.insert(TABLE_USERS, null, values);
        db.close();
        return id;
    }

    // 添加管理员
    public long addAdmin(String username, String password) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_ADMIN_USERNAME, username);
        values.put(COLUMN_ADMIN_PASSWORD, password);
        long id = db.insert(TABLE_ADMINS, null, values);
        db.close();
        return id;
    }

    // 验证用户登录
    public boolean checkUserLogin(String username, String password) {
        SQLiteDatabase db = this.getReadableDatabase();
        String[] columns = {COLUMN_USER_ID};
        String selection = COLUMN_USER_USERNAME + " = ?" + " AND " + COLUMN_USER_PASSWORD + " = ?";
        String[] selectionArgs = {username, password};
        Cursor cursor = db.query(TABLE_USERS, columns, selection, selectionArgs, null, null, null);
        int count = cursor.getCount();
        cursor.close();
        db.close();
        return count > 0;
    }

    // 验证管理员登录
    public boolean checkAdminLogin(String username, String password) {
        SQLiteDatabase db = this.getReadableDatabase();
        String[] columns = {COLUMN_ADMIN_ID};
        String selection = COLUMN_ADMIN_USERNAME + " = ?" + " AND " + COLUMN_ADMIN_PASSWORD + " = ?";
        String[] selectionArgs = {username, password};
        Cursor cursor = db.query(TABLE_ADMINS, columns, selection, selectionArgs, null, null, null);
        int count = cursor.getCount();
        cursor.close();
        db.close();
        return count > 0;
    }

    // 获取所有用户
    public List<UserAccount> getAllUsers() {
        List<UserAccount> userList = new ArrayList<>();
        String selectQuery = "SELECT * FROM " + TABLE_USERS;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        if (cursor.moveToFirst()) {
            do {
                UserAccount user = new UserAccount();
                user.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_USER_ID)));
                user.setUsername(cursor.getString(cursor.getColumnIndex(COLUMN_USER_USERNAME)));
                user.setPassword(cursor.getString(cursor.getColumnIndex(COLUMN_USER_PASSWORD)));
                userList.add(user);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return userList;
    }

    // 获取所有管理员
    public List<UserAccount> getAllAdmins() {
        List<UserAccount> adminList = new ArrayList<>();
        String selectQuery = "SELECT * FROM " + TABLE_ADMINS;
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(selectQuery, null);

        if (cursor.moveToFirst()) {
            do {
                UserAccount admin = new UserAccount();
                admin.setId(cursor.getInt(cursor.getColumnIndex(COLUMN_ADMIN_ID)));
                admin.setUsername(cursor.getString(cursor.getColumnIndex(COLUMN_ADMIN_USERNAME)));
                admin.setPassword(cursor.getString(cursor.getColumnIndex(COLUMN_ADMIN_PASSWORD)));
                adminList.add(admin);
            } while (cursor.moveToNext());
        }
        cursor.close();
        db.close();
        return adminList;
    }

    // 清空用户表
    public void clearUsers() {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_USERS, null, null);
        db.close();
    }

    // 清空管理员表
    public void clearAdmins() {
        SQLiteDatabase db = this.getWritableDatabase();
        db.delete(TABLE_ADMINS, null, null);
        db.close();
    }

    // 以下方法为了兼容性保留

    public boolean insertData(String date, String batch, String number,
                           String w1, String w2, String w3) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put("date", date);
        cv.put("batch_number", batch);
        cv.put("number", number);
        cv.put("weight1", w1);
        cv.put("weight2", w2);
        cv.put("weight3", w3);
        long result = db.insert(TABLE_RECORDS, null, cv);
        return result != -1;
    }

    public List<WeightRecord> getAllData() {
        List<WeightRecord> records = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT * FROM " + TABLE_RECORDS, null);

        if (cursor.moveToFirst()) {
            do {
                WeightRecord record = new WeightRecord(
                        cursor.getString(1), // date
                        cursor.getString(2), // batch
                        cursor.getString(3), // number
                        cursor.getString(4), // weight1
                        cursor.getString(5), // weight2
                        cursor.getString(6)  // weight3
                );
                records.add(record);
            } while (cursor.moveToNext());
        }
        cursor.close();
        return records;
    }

    public List<WeightRecord> getPagedData(int page, int pageSize) {
        List<WeightRecord> records = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();

        String query = "SELECT * FROM " + TABLE_RECORDS
                + " LIMIT " + pageSize
                + " OFFSET " + (page-1)*pageSize;

        Cursor cursor = db.rawQuery(query, null);

        if (cursor.moveToFirst()) {
            do {
                WeightRecord record = new WeightRecord(
                        cursor.getString(1),
                        cursor.getString(2),
                        cursor.getString(3),
                        cursor.getString(4),
                        cursor.getString(5),
                        cursor.getString(6)
                );
                records.add(record);
            } while (cursor.moveToNext());
        }
        cursor.close();
        return records;
    }

    public int getTotalCount() {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM " + TABLE_RECORDS, null);
        cursor.moveToFirst();
        int count = cursor.getInt(0);
        cursor.close();
        return count;
    }

    public List<WeightRecord> searchByMaterialId(String materialId) {
        List<WeightRecord> records = new ArrayList<>();
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery(
                "SELECT * FROM " + TABLE_RECORDS + " WHERE number = ?",
                new String[]{materialId}
        );

        if (cursor.moveToFirst()) {
            do {
                WeightRecord record = new WeightRecord(
                        cursor.getString(1), // date
                        cursor.getString(2), // batch_number
                        cursor.getString(3), // number (物料编号)
                        cursor.getString(4), // weight1
                        cursor.getString(5), // weight2
                        cursor.getString(6)  // weight3
                );
                records.add(record);
            } while (cursor.moveToNext());
        }
        cursor.close();
        return records;
    }

    // 更新用户信息
    public boolean updateUser(int id, String username, String password) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_USER_USERNAME, username);
        values.put(COLUMN_USER_PASSWORD, password);
        int rowsAffected = db.update(TABLE_USERS, values, COLUMN_USER_ID + " = ?", 
                new String[]{String.valueOf(id)});
        db.close();
        return rowsAffected > 0;
    }

    // 更新管理员信息
    public boolean updateAdmin(int id, String username, String password) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues values = new ContentValues();
        values.put(COLUMN_ADMIN_USERNAME, username);
        values.put(COLUMN_ADMIN_PASSWORD, password);
        int rowsAffected = db.update(TABLE_ADMINS, values, COLUMN_ADMIN_ID + " = ?", 
                new String[]{String.valueOf(id)});
        db.close();
        return rowsAffected > 0;
    }

    public boolean insertMysqlData(String date, String batch, String number,
                                   String w1, String w2, String w3) {
        SQLiteDatabase db = this.getWritableDatabase();
        ContentValues cv = new ContentValues();
        cv.put("date", date);
        cv.put("batch_number", batch);
        cv.put("number", number);
        cv.put("weight1", w1);
        cv.put("weight2", w2);
        cv.put("weight3", w3);
        long result = db.insert(TABLE_MYSQL, null, cv);
        return result != -1;
    }

    public int getmysqlTotalCount() {
        SQLiteDatabase db = this.getReadableDatabase();
        Cursor cursor = db.rawQuery("SELECT COUNT(*) FROM " + TABLE_RECORDS, null);
        cursor.moveToFirst();
        int count = cursor.getInt(0);
        cursor.close();
        return count;
    }
}
